
USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------

--Enable CLR Integration if is not enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'show advanced options' AND [value] = 1) BEGIN 
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	PRINT '<<< Enable SQL Advanced Options - Completed with SUCCESS >>>'
END

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'clr enabled' AND [value] = 1) BEGIN 
	EXEC sp_configure 'clr enabled', 1
	RECONFIGURE
	PRINT '<<< Enable CLR - Completed with SUCCESS >>>'
END

-- Drop existing functions so assembly can be dropped
IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'fn_regex_like') BEGIN 
	DROP SYNONYM [clr].[fn_regex_like]
	PRINT '<<< DROP SYNONYM [clr].[fn_regex_like] - Completed with SUCCESS >>>'
END


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_ismatch' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_regex_ismatch]
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_ismatch] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_replace' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_regex_replace
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_replace] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_matches' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_regex_matches
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_matches] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_split' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_regex_split
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_ismatch] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_occurs' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_regex_occurs
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_occurs] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_instr' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_regex_instr
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_instr] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_substr' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_regex_substr
	PRINT '<<< DROP FUNCTION [clr].[fn_regex_substr] - Completed with SUCCESS >>>'
END


--Drop the assembly if it exist
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrRegEx') BEGIN 
	DROP ASSEMBLY [ClrRegEx]
	PRINT '<<< DROP Assembly ClrRegEx - Completed with SUCCESS >>>'
END

-- Create the assembly if it does not exists
CREATE ASSEMBLY [ClrRegEx] 
FROM 'C:\Program Files\Microsoft SQL Server\100\CLR\ClrRegEx.dll' 
WITH PERMISSION_SET = SAFE
	
PRINT '<<< CREATE Assembly ClrRegEx - Completed with SUCCESS >>>'


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			[clr].[fn_regex_ismatch]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic matching  
Parameters:		@input = 
				@pattern = 
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_ismatch('Hello World', 'w', 1)	--Ignores Case
				SELECT clr.fn_regex_ismatch('Hello World', 'w', 0)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_ismatch&referringTitle=Documentation		
Version:		20121019
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/19/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_regex_ismatch] 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @ignore_case BIT)
RETURNS BIT	
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExIsMatch

GO

CREATE SYNONYM [clr].[fn_regex_like] FOR [Lib].[clr].[fn_regex_ismatch]

GO 

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_ismatch' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_ismatch] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_ismatch] - Completed with ERROR >>>'
END

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'fn_regex_like') BEGIN 
	PRINT '<<< CREATE SYNONYM [clr].[fn_regex_like] - Completed with SUCCESS >>>'
END
ELSE BEGIN
	PRINT '<<< CREATE SYNONYM [clr].[fn_regex_like] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------











/*================================================================================================
Name:			[clr].[fn_regex_replace]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic replacing  
Parameters:		@input = 
				@pattern =
				@replacement =  
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_replace('Hello World', 'h', 'J', 1)	--Ignores Case
				SELECT clr.fn_regex_replace('Hello World', 'h', 'J', 1)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_replace&referringTitle=Documentation		
Version:		20121019
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/19/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_regex_replace] 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @replacement NVARCHAR(MAX)
	, @ignore_case BIT)	
RETURNS NVARCHAR(MAX)	
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExReplace
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_replace' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_replace] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_replace] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_regex_matches]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic matching  
Parameters:		@input = 
				@pattern = 
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_matches('Hello World', 'L', 1)	--Ignores Case
				SELECT clr.fn_regex_matches('Hello World', 'L', 0)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_matches&referringTitle=Documentation		
Version:		20121019
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/19/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].fn_regex_matches 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @ignore_case BIT)
RETURNS TABLE (match NVARCHAR(MAX), [index] INT, [length] INT) 
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExMatches
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_matches' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_matches] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_matches] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_regex_split]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic split function 
Parameters:		@input = 
				@pattern = 
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_split('Hello World', 'w', 1)	--Ignores Case
				SELECT clr.fn_regex_split('Hello World', 'w', 0)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_split&referringTitle=Documentation		
Version:		20121019
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/19/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].fn_regex_split 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @ignore_case BIT)
RETURNS TABLE (match NVARCHAR(MAX)) 
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExSplit
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_split' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_split] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_split] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------



/*================================================================================================
Name:			[clr].[fn_regex_occurs]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic occurence  
Parameters:		@input = 
				@pattern = 
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_occurs('Hello World', 'o', 1)	--Ignores Case
				SELECT clr.fn_regex_occurs('Hello World', 'o', 0)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_occurs&referringTitle=Documentation		
Version:		20121019
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/19/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].fn_regex_occurs 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @ignore_case BIT)
RETURNS BIGINT	
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExOccurs
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_occurs' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_occurs] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_occurs] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


/*================================================================================================
Name:			[clr].[fn_regex_instr]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic instr  
Parameters:		@input = 
				@pattern = 
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_instr('Hello World', 'o', 1)	--Ignores Case
				SELECT clr.fn_regex_instr('Hello World', 'o', 0)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_instr&referringTitle=Documentation		
Version:		20121022
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/22/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].fn_regex_instr 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @ignore_case BIT)
RETURNS BIGINT	
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExInstr
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_instr' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_instr] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_instr] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


/*================================================================================================
Name:			[clr].[fn_regex_substr]
Type:			Custom aggregate 
Owner:					
Description:	Fuzzy logic substring 
Parameters:		@input = 
				@pattern = 
				@ignore_case = 
Dependens on:  	ClrRegex.dll
Usage:			SELECT clr.fn_regex_substr('Hello World', 'o', 1, 1, 1)	--Ignores Case
				SELECT clr.fn_regex_substr('Hello World', 'o', 0, 1, 1)	--Case Sensitive
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_regex_occurs&referringTitle=Documentation		
Version:		20121022
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/22/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].fn_regex_substr 
	( @input NVARCHAR(MAX)
	, @pattern NVARCHAR(MAX)
	, @ignore_case BIT
	, @start BIGINT
	, @occurence BIGINT)
RETURNS NVARCHAR(MAX)	
AS EXTERNAL NAME ClrRegex.[RegexTools.RegexFn].RegExSubstr
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_regex_substr' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_substr] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_regex_substr] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


